package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import domain.Jugador;

public class JDBCJugadorDAO implements IJugadorDAO{
	
	private Connection conn;
	
	public JDBCJugadorDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
	protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }

	
	public void insertJugador(Jugador jugador, String oid) {
		String sql = "INSERT INTO jugadores (oid,nombre,dorsal,demarcacion," +
		"peso_altura,edad,procedencia)VALUES (?,?,?,?,?,?,?)";
		PreparedStatement stmt = null;

		try {
		    stmt = conn.prepareStatement(sql);
		
		    stmt.setString(1,oid);
		    stmt.setString(2, jugador.getNombre());
		    stmt.setInt(3, jugador.getDorsal());
		    stmt.setString(4, jugador.getDemarcacion());
		    stmt.setString(5, jugador.getPeso_altura());
		    stmt.setInt(6, jugador.getEdad());
		    stmt.setString(7, jugador.getProcedencia());
		
		    stmt.executeUpdate();
		
		} catch (SQLException e) {
		    System.out.println("Message: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
		    try {
		        if (stmt != null) {
		            stmt.close();
		        }
		    } catch (SQLException e) {
		    }
		}	
		
	}

	
	public void deleteJugador(Jugador jugador) {
		String sql = "DELETE FROM jugadores WHERE (nombre = ?)";
		PreparedStatement stmt = null;
		
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setString(1, jugador.getNombre());
			
			stmt.executeUpdate();
			
		}catch (SQLException e) {
		    System.out.println("Message: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
		    try {
		        if (stmt != null) {
		            stmt.close();
		        }
		    } catch (SQLException e) {
		    }
		}
	}
	
	public void updateDorsal (String id,Integer dorsal){
		String sql = "UPDATE jugadores SET dorsal = ? WHERE oid = ?";
		
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			
			stmt.setInt(1, dorsal);
			stmt.setString(2, id);
			
			stmt.executeUpdate();
		}
	catch (SQLException e) {
		
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			}
		}
		
	}
	
	public void updateEdad (String id,Integer edad){
		String sql = "UPDATE jugadores SET edad = ? WHERE oid = ?";
		
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			
			stmt.setInt(1, edad);
			stmt.setString(2, id);
			
			stmt.executeUpdate();
		}
	catch (SQLException e) {
		
		System.out.println("Message: " + e.getMessage());
		System.out.println("SQLState: " + e.getSQLState());
		System.out.println("ErrorCode: " + e.getErrorCode());
	} finally {
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			}
		}
		
	}

	
	public String obtnerOID(String nombre) {
		String res = "";
		String sql = "SELECT oid FROM jugadores WHERE nombre =?";
		PreparedStatement stmt = null;
		ResultSet result = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nombre);

			result = stmt.executeQuery();

			if (result.next()) {
				res = result.getString(1);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());

		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
		return res;	
	}

}
